﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace POSPlus.EZBarTender.DatabaseOperations
{
	public class SignData : DatabaseObject
	{
		private String _upc;
		private String _desc1;
		private String _desc2;
		private String _desc3;
		private String _desc4;
		private String _desc5;

		public String Upc { get { return _upc; }
			set 
			{
				_upc = value;
				_upc = _upc.Trim();
				while (_upc.Length < 13)
				{
					_upc = "0" + _upc;
				}
			}
		}
		public String Desc1 { get{return _desc1;}
			set
			{
				if(value != null)
					_desc1 = value.Trim();
			}
		}
		public String Desc2 { get { return _desc2; }
			set
			{
				if (value != null)
					_desc2 = value.Trim();
			}
		}
		public String Desc3 { get { return _desc3; }
			set
			{
				if (value != null)
					_desc3 = value.Trim();
			}
		}
		public String Desc4 { get { return _desc4; }
			set
			{
				if (value != null)
					_desc4 = value.Trim();
			}
		}
		public String Desc5 { get { return _desc5; }
			set
			{
				if (value != null)
					_desc5 = value.Trim();
			}
		}
		public int Spm { get; set; }
		public double SalePrice { get; set; }
		public DateTime WeekEnding { get; set; }
		public int Store { get; set; }
		public String SaleType { get; set; }
		public String SignSize { get; set; }

		public SignData(String connectionString)
		{
			ConnectionString = connectionString;
			PopulateDefault();
		}

		public SignData(String connectionString, String upc, DateTime weekEnding, int store)
		{
			ConnectionString = connectionString;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT upc, Desc1, Desc2, Desc3, Desc4, Desc5, " +
					"Spm, SalePrice, WeekEnding, Store, Sale_Type, Sign_Size FROM SignData WHERE Upc = @Upc AND WeekEnding = @WeekEnding " +
					"AND Store = @Store", connection))
				{
					command.Parameters.AddWithValue("@Upc", upc);
					command.Parameters.AddWithValue("@WeekEnding", weekEnding);
					command.Parameters.AddWithValue("@Store", store);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							Upc = Convert.ToString(reader["Upc"]);
							Desc1 = Convert.ToString(reader["Desc1"]);
							Desc2 = Convert.ToString(reader["Desc2"]);
							Desc3 = Convert.ToString(reader["Desc3"]);
							Desc4 = Convert.ToString(reader["Desc4"]);
							Desc5 = Convert.ToString(reader["Desc5"]);
							Spm = Convert.ToInt32(reader["Spm"]);
							SalePrice = Convert.ToDouble(reader["SalePrice"]);
							WeekEnding = Convert.ToDateTime(reader["WeekEnding"]);
							Store = Convert.ToInt32(reader["Store"]);
							SaleType = Convert.ToString(reader["Sale_Type"]);
							SignSize = Convert.ToString(reader["Sign_Size"]);
						}
						else
							PopulateDefault();
						reader.Close();
					}
					connection.Close();
				}
			}
		}

		public static List<SignData> GetAllWithStoreNumber(string connectionString, int storeNumber)
		{
			List<SignData> signData = new List<SignData>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM SignData WHERE Store=@Store", connection))
				{
					command.Parameters.AddWithValue("@Store", storeNumber);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SignData sign = new SignData(connectionString);
							sign.Desc1 = DatabaseObject.FromDB<String>(reader["Desc1"]);
							sign.Desc2 = DatabaseObject.FromDB<String>(reader["Desc2"]);
							sign.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							sign.Desc4 = DatabaseObject.FromDB<String>(reader["Desc4"]);
							sign.Desc5 = DatabaseObject.FromDB<String>(reader["Desc5"]);
							sign.SalePrice = (double)DatabaseObject.FromDB<decimal>(reader["SalePrice"]);
							sign.SaleType = DatabaseObject.FromDB<String>(reader["Sale_Type"]);
							sign.SignSize = DatabaseObject.FromDB<String>(reader["Sign_Size"]);
							sign.Spm = DatabaseObject.FromDB<Int32>(reader["Spm"]);
							sign.Store = DatabaseObject.FromDB<Int32>(reader["Store"]);
							sign.Upc = DatabaseObject.FromDB<String>(reader["Upc"]);
							sign.WeekEnding = DatabaseObject.FromDB<DateTime>(reader["WeekEnding"]);
							signData.Add(sign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return signData;
		}

		public static List<SignData> GetAll(String connectionString)
		{
			List<SignData> signData = new List<SignData>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM SignData", connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SignData data = new SignData(connectionString);
							data.Desc1 = DatabaseObject.FromDB<String>(reader["Desc1"]);
							data.Desc2 = DatabaseObject.FromDB<String>(reader["Desc2"]);
							data.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							data.Desc4 = DatabaseObject.FromDB<String>(reader["Desc4"]);
							data.Desc5 = DatabaseObject.FromDB<String>(reader["Desc5"]);
							data.SalePrice = DatabaseObject.FromDB<Double>(reader["SalePrice"]);
							data.SaleType = DatabaseObject.FromDB<String>(reader["Sale_Type"]);
							data.SignSize = DatabaseObject.FromDB<String>(reader["Sign_Size"]);
							data.Spm = DatabaseObject.FromDB<Int32>(reader["Spm"]);
							data.Store = DatabaseObject.FromDB<Int32>(reader["Store"]);
							data.Upc = DatabaseObject.FromDB<String>(reader["Upc"]);
							data.WeekEnding = DatabaseObject.FromDB<DateTime>(reader["WeekEnding"]);
							signData.Add(data);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return signData;
		}

		protected override void PopulateDefault()
		{
			Upc = "";
			Desc1 = "";
			Desc2 = "";
			Desc3 = "";
			Spm = 0;
			SalePrice = 0;
			Desc4 = "";
			Desc5 = "";
			WeekEnding = new DateTime(1900, 1, 1);
			Store = 0;
			SaleType = "";
			SignSize = "";
		}

		public override Boolean Create()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("INSERT INTO SignData " +
					"(Upc, Desc1, Desc2, Desc3, Spm, SalePrice, Desc4, Desc5, WeekEnding, Store, " +
					"Sale_Type, Sign_Size) VALUES (@Upc, @Desc1, @Desc2, @Desc3, @Spm, @SalePrice, " +
					"@Desc4, @Desc5, @WeekEnding, @Store, @SaleType, @SignSize)", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@Desc1", Desc1);
					command.Parameters.AddWithValue("@Desc2", Desc2);
					command.Parameters.AddWithValue("@Desc3", Desc3);
					command.Parameters.AddWithValue("@Spm", Spm);
					command.Parameters.AddWithValue("@SalePrice", SalePrice);
					command.Parameters.AddWithValue("@Desc4", Desc4);
					command.Parameters.AddWithValue("@Desc5", Desc5);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@Store", Store);
					command.Parameters.AddWithValue("@SaleType", SaleType);
					command.Parameters.AddWithValue("@SignSize", SignSize);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		public override Boolean Update()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("UPDATE SignData SET " +
					"Upc = @Upc, Desc1 = @Desc1, Desc2 = @Desc2, Desc3 = @Desc3, Spm = @Spm, " +
					"SalePrice = @SalePrice, Desc4 = @Desc4, Desc5 = @Desc5, WeekEnding = @WeekEnding, " +
					"Store = @Store, Sale_Type = @SaleType, Sign_Size = @SignSize WHERE " +
					"Upc = @Upc AND WeekEnding = @WeekEnding AND Store = @Store", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@Desc1", Desc1);
					command.Parameters.AddWithValue("@Desc2", Desc2);
					command.Parameters.AddWithValue("@Desc3", Desc3);
					command.Parameters.AddWithValue("@Spm", Spm);
					command.Parameters.AddWithValue("@SalePrice", SalePrice);
					command.Parameters.AddWithValue("@Desc4", Desc4);
					command.Parameters.AddWithValue("@Desc5", Desc5);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@Store", Store);
					command.Parameters.AddWithValue("@SaleType", SaleType);
					command.Parameters.AddWithValue("@SignSize", SignSize);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		public override Boolean Delete()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("DELETE FROM SignsData " +
					"WHERE Upc = @Upc AND WeekEnding = @WeekEnding AND Store = @Store", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@Store", Store);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
					Upc = "";
					WeekEnding = new DateTime(1900, 1, 1);
					Store = 0;
				}
			}
			return rowsAffected > 0;
		}
	}
}
